/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import data.AkademikData;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Muhammad Dony
 */
public class AkademikDB extends ConnectDB {

    private String sql;
    private Connection con;
    private Statement stmt;
    private ResultSet rs;
    
    public List getakademik() throws SQLException, ClassNotFoundException{
        sql = "SELECT * from `akademik`";
        return getakademikdata(sql);
    }
    
    public List getakademikById(String ID) throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `akademik` WHERE `ID`='"+ID+"';";
        return getakademikdata(sql);
    }
    
    public List getakademikBySemester(String semester) throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `akademik` WHERE `semester`='"+semester+"';";
        return getakademikdata(sql);
    }
    
    public List getakademikByxx(String ID, int no) throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `akademik` WHERE `ID`='"+ID+"' AND `nomor`='"+no+"';";
        return getakademikdata(sql);
    }
    public List getakademikBy0() throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `akademik` WHERE `status`='0';";
        return getakademikdata(sql);
    }
        
     private List getakademikdata(String sql) throws SQLException, ClassNotFoundException{
        // mempersiapkan list dataPelaporan
        List listakademik = new ArrayList();
        //membuat koneksi , createConnection terdapat dalam DBClass
        con = createConnection();
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        // mempersiapkan object dataPelaporan
        AkademikData akademikUser;
        
        while(rs.next()){
            akademikUser = new AkademikData();
            akademikUser.setNomor(rs.getInt(1));
            akademikUser.setID(rs.getString(2));
            akademikUser.setSemester(rs.getString(3));
            akademikUser.setIPS(rs.getString(4));
            akademikUser.setIPK(rs.getString(5));
            akademikUser.setStatus(rs.getString(6));
            listakademik.add(akademikUser);
        }
        // menutup semua koneksi db
        rs.close();
        stmt.close();
        con.close();
        //return value list data pelaporan
        return listakademik;
        
    }
    
    private void actakademik(String sql) throws ClassNotFoundException, SQLException{
        // membuat koneksi
        con = createConnection();
        stmt = con.createStatement();
        // execute query
        stmt.execute(sql);
        // menutup koneksi db
        stmt.close();
        con.close();
    }
   public void insertAkademik(AkademikData listAkademik) throws ClassNotFoundException, SQLException{
        // query
        sql = "INSERT INTO `scholarship`.`akademik` (`ID`, `semester`, `ips`, `ipk`, `status`) VALUES ('"
                +listAkademik.getID()+"', '"
                +listAkademik.getSemester()+"', '"
                +listAkademik.getIPS()+"', '"
                +listAkademik.getIPK()+"', '"
                +listAkademik.getStatus()+"');";
        actakademik(sql);
    }
   

   //// EDIT DAN DELETE MASIH BERMASALAH Y!!!!!
   
   
    public void updateAkademik(AkademikData listAkademik) throws ClassNotFoundException, SQLException{
        // query
        sql = "UPDATE `akademik` SET `status`='"+listAkademik.getStatus()+
                "' WHERE `ID`='"+listAkademik.getID()+"' AND nomor="+listAkademik.getNomor()+";";
        // memanggil method untuk execute query
        actakademik(sql);
    }
    public void updateStatusAkademik(AkademikData listAkademik) throws ClassNotFoundException, SQLException{
        // query
        sql = "UPDATE `akademik` SET `semester`='"+listAkademik.getSemester()+
                "',`ips`='"+listAkademik.getIPS()+
                "',`ipk`='"+listAkademik.getIPK()+
                "',`status`='"+listAkademik.getStatus()+
                "' WHERE `ID`='"+listAkademik.getID()+"' AND nomor="+listAkademik.getNomor()+";";
        // memanggil method untuk execute query
        actakademik(sql);
    }
    public void deleteAkademik(String ID, int akademik) throws ClassNotFoundException, SQLException{
        sql = "DELETE FROM `akademik` WHERE `ID`='"+ID+"' AND `nomor`='"+akademik+"';";
        actakademik(sql);
    } 
    
    
    
}
